﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using HJT212_Protocol_DAL.Model;

namespace HJT212_Protocol_DAL.DAO
{
    public class CMD_SendDao : BaseDao
    {



        public CMD_SendDao()
        {

        }

        public CMD_SendDao(string conn)
        {
            connStr = conn;
        }


        /// <summary>
        /// 根据MN号检索发送的指令缓存
        /// </summary>
        /// <param name="MN">MN号</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="PageNum">页码</param>
        /// <returns>符合条件的数据</returns>
        public List<CMD_SendModel> GetSendCMDByMN(string MN, int pageSize, int PageNum)
        {
            try
            {
//                string SQL =
//                    @"
//                SELECT TOP {0} * 
//                 FROM (       
//                 SELECT ROW_NUMBER() OVER (ORDER BY sid) AS RowNumber,* FROM CMD_Send  
//                   where MN='{2}' And isSend<>1
//                ) A6 WHERE RowNumber > {0}*({1}-1)
//                ";

                string SQL = @"
                        SELECT TOP {0} *
                         FROM CMD_Send
                         WHERE ID NOT IN (SELECT TOP {1} ID FROM CMD_Send where MN = '{2}'
                          And isSend <> 1 ORDER BY ID) 
                          AND MN = '{2}' And isSend <> 1
                         ORDER BY ID ";
                SQL = string.Format(SQL, pageSize, pageSize * (PageNum - 1), MN);
                DataTable dt = helper.Data_Table(SQL);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return HJ212_Common.DataConvert.DataTableToList<CMD_SendModel>(dt);
                }
            }
            catch (Exception ex)
            {
                log.Error("出现异常", ex);
            }
            return null;
        }


        /// <summary>
        /// 根据QN号检索发送的指令缓存
        /// </summary>
        /// <param name="QN">QN</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="PageNum">页码</param>
        /// <returns>符合条件的数据</returns>
        public List<CMD_SendModel> GetSendCMDByQN(string QN, int pageSize, int PageNum)
        {
            try
            {
//                string SQL =
//                    @"
//                SELECT TOP {0} * 
//                 FROM (       
//                 SELECT ROW_NUMBER() OVER (ORDER BY sid) AS RowNumber,* FROM CMD_Send  
//                   where QN='{2}' And isSend<>1
//                ) A6 WHERE RowNumber > {0}*({1}-1)
//                ";
                string SQL = @"
                        SELECT TOP {0} *
                         FROM CMD_Send
                         WHERE ID NOT IN (SELECT TOP {1} ID FROM CMD_Send where QN='{2}'
                          And isSend <> 1 ORDER BY ID)
                          AND QN='{2}' And isSend <> 1
                         ORDER BY ID ";
                SQL = string.Format(SQL, pageSize, pageSize * (PageNum - 1), QN);
                DataTable dt = helper.Data_Table(SQL);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return HJ212_Common.DataConvert.DataTableToList<CMD_SendModel>(dt);
                }
            }
            catch (Exception ex)
            {

                log.Error("出现异常", ex);
            }
            return null;
        }
        /// <summary>
        /// 根据SID号检索发送的指令缓存
        /// </summary>
        /// <param name="SID">SID</param>
        /// <param name="pageSize">页大小</param>
        /// <param name="PageNum">页码</param>
        /// <returns>符合条件的数据</returns>
        public List<CMD_SendModel> GetSendCMDBySID(string SID, int pageSize, int PageNum)
        {
            try
            {
//                string SQL =
//                    @"
//                SELECT TOP {0} * 
//                 FROM (       
//                 SELECT ROW_NUMBER() OVER (ORDER BY sid) AS RowNumber,* FROM CMD_Send  
//                   where SID='{2}' And isSend<>1
//                ) A6 WHERE RowNumber > {0}*({1}-1)
//                ";
                string SQL = @"
                        SELECT TOP {0} *
                         FROM CMD_Send
                         WHERE ID NOT IN (SELECT TOP {1} ID FROM CMD_Send where SID='{2}'
                          And isSend <> 1 ORDER BY ID)
                          AND SID='{2}' And isSend <> 1
                         ORDER BY ID ";
                SQL = string.Format(SQL, pageSize, pageSize * (PageNum - 1), SID);
                DataTable dt = helper.Data_Table(SQL);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return HJ212_Common.DataConvert.DataTableToList<CMD_SendModel>(dt);
                }
            }
            catch (Exception ex)
            {
                log.Error("出现异常", ex);
            }
            return null;
        }
        /// <summary>
        /// 把指定的CMD指令写入缓存
        /// </summary>
        /// <param name="cmd">指令</param>
        public void AddCMDToCache(CMD_SendModel cmd)
        {
            try
            {
                #region SQL
                string sql =
            @"INSERT INTO 
                  CMD_Send
                (
                  SID,
                  ST,
                  CN,
                  MN,
                  PW,
                  QN,
                  CP,
                  isSend
                ) 
                VALUES (
                  '{0}',
                  '{1}',
                  '{2}',
                  '{3}',
                  '{4}',
                  '{5}',
                  '{6}',
                  {7}
                ) ";
                sql = string.Format(sql, cmd.SID, cmd.ST, cmd.CN, cmd.MN, cmd.PW, cmd.QN, cmd.CP, cmd.isSend);
                #endregion

                helper.ExecuteNonQueryTransaction(new List<string>() { sql });
            }
            catch (Exception ex)
            {
                log.Error("出现异常", ex);
            }
        }

        /// <summary>
        /// 更新CMD发送状态为已发送
        /// </summary>
        /// <param name="cmd">指令信息</param>
        public void UpdateSendState(CMD_SendModel cmd)
        {
            try
            {
                if (cmd == null) return;

                string sqlUpdate = "Update CMD_Send Set isSend=1 where ID='{0}' ";
                sqlUpdate = string.Format(sqlUpdate, cmd.ID);
                helper.ExecuteNonQuery(sqlUpdate);
            }
            catch (Exception ex)
            {
                log.Error("出现异常", ex);
            }
        }

        public string GetSidByQn(string Qn, out string content)
        {
            content = null;
            try
            {
                if (string.IsNullOrEmpty(Qn)) return null;
                string SQL_SELECT_DATA = "select top 1 SID,Content from CMD_Send where QN='{0}' ";
                SQL_SELECT_DATA = string.Format(SQL_SELECT_DATA, Qn);

                DataTable dt = helper.Data_Table(SQL_SELECT_DATA);

                if (dt != null && dt.Rows.Count > 0)
                {
                    string Sid = string.Empty;
                    Sid = string.Format("{0}", dt.Rows[0][0]);
                    content = string.Format("{0}", dt.Rows[0][1]);
                    return Sid;
                }
            }
            catch (Exception ex)
            {
                log.Error("出现异常", ex);
            }
            return null;
        }
    }
}
